<!DOCTYPE html><html><head><title>MySQL学习笔记（Day001-002：介绍和安装）</title><meta charset='utf-8'><link href='https://dn-maxiang.qbox.me/res-min/themes/marxico.css' rel='stylesheet'></head><body><div id='preview-contents' class='note-content'>
                        <div id="wmd-preview" class="preview-content"></div>
                    <div id="wmd-preview-section-17" class="wmd-preview-section preview-content">

</div><div id="wmd-preview-section-72" class="wmd-preview-section preview-content">

<h1 id="mysql学习笔记day001-002介绍和安装">MySQL学习笔记（Day001-002：介绍和安装）</h1>

<p><p class="note-tags "><code class="notebook">MySQL学习</code> </p></p>

<div><div class="toc"><div class="toc">
<ul>
<li><a href="#mysql学习笔记day001-002介绍和安装">MySQL学习笔记（Day001-002：介绍和安装）</a><ul>
<li><a href="#一mysql版本选择">一.MySQL版本选择</a></li>
<li><a href="#二-mysql官方网站介绍">二. MySQL官方网站介绍</a></li>
<li><a href="#三-mysql下载">三. MySQL下载</a></li>
<li><a href="#四-mysql安装">四. MySQL安装</a></li>
<li><a href="#五-附录">五. 附录</a></li>
</ul>
</li>
</ul>
</div>
</div>
</div>

</div><div id="wmd-preview-section-2142" class="wmd-preview-section preview-content">

<h2 id="一mysql版本选择">一.MySQL版本选择</h2>

<ol><li rel="1">MySQL5.6以后的版本，推荐使用官方版本。</li>
<li rel="2">Percona：在5.6版本以后，MySQL将Percon之前优化集成到官方版本中;</li>
<li rel="3">MariaDB：无INNODB;且核心代码较老</li>
<li rel="4">MySQL在5.6以后不断重构源码，安装包越来越大，功能和性能在持续改进</li>
</ol>

<hr></div><div id="wmd-preview-section-2148" class="wmd-preview-section preview-content">

<h2 id="二-mysql官方网站介绍">二. MySQL官方网站介绍</h2>

<p>官方网站：<a href="http://www.mysql.com" target="_blank">http://www.mysql.com</a></p>

<ol><li rel="1"><p><strong>Developer Zone</strong>： MySQL开发工程师板块</p>

<ul>
<li>Articles： Oracle工程师自己的博客</li>
<li>Plant MySQL： 和MySQL相关从业人员的博客</li>
<li>Bugs：MySQL BugList</li>
<li>Worklog：开发记录</li>
<li>Labs：MySQL实验性项目</li></ul></li>
<li rel="2"><p><strong>Downloads</strong>：MySQL下载</p>

<ul>
<li>Enterprise：MySQL企业版本相关，略过</li>
<li>Community：社区版，我们下载和使用社区版 <br>
<ul>
<li>MySQL Community Server：MySQL Server</li>
<li>MySQL Fabric : 和管理相关的工具</li>
<li>MySQL Router：路由中间件</li>
<li>MySQL Utilities：MySQL应用程序包</li>
<li>MySQL Workbench：官方图型化管理界面</li>
<li>MySQL Proxy：MySQL代理。Alpha版本，不推荐</li></ul></li></ul></li>
<li rel="3"><p><strong>Documentation</strong>：MySQL文档</p>

<ul>
<li>官方文档 版面更改，下载离线文档在左侧Menu的下面 <br>
<ul>
<li><a href="http://downloads.mysql.com/docs/refman-5.7-en.a4.pdf" target="_blank">PDF A4</a></li>
<li><a href="http://downloads.mysql.com/docs/refman-5.7-en.epub" target="_blank">EPUB</a></li>
<li><a href="http://downloads.mysql.com/docs/refman-5.7-en.html-chapter.zip" target="_blank">HTML</a></li></ul></li></ul></li>
</ol>

</div><div id="wmd-preview-section-2149" class="wmd-preview-section preview-content">

<hr></div><div id="wmd-preview-section-3253" class="wmd-preview-section preview-content">

<h2 id="三-mysql下载">三. MySQL下载</h2>

<ol><li rel="1">推荐下载<code>Linux-Generic</code>版本</li>
<li rel="2"><code>Source Code</code>版本主要作用是为了让开发人员研究源码使用，自己编译对性能提升不明显</li>
<li rel="3">不推荐<code>Version 5.5.X</code>，有部分bug</li>
<li rel="4">推荐使用<code>Version 5.6.X</code>和<code>Version 5.7.X</code></li>
</ol>

<blockquote>
  <p><em>下载地址：</em> <br>
  <a href="http://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.9-linux-glibc2.5-x86_64.tar.gz" target="_blank">MySQL Community Server 5.7.9 Linux Generic x86-64bit</a> <br>
  <a href="http://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.27-linux-glibc2.5-x86_64.tar.gz" target="_blank">MySQL Community Server 5.6.27 Linux Generic x86-64bit</a></p>
</blockquote>

<hr></div><div id="wmd-preview-section-7020" class="wmd-preview-section preview-content">

<h2 id="四-mysql安装">四. MySQL安装</h2>

<ol><li rel="1"><p>安装通用步骤：</p>

<ul>
<li>解压缩<code>mysql-VERSION-linux-glibc2.5-x86_64.tar.gz</code></li>
<li>打开<code>INSTALL_BINARY</code> 文件，按照<code>shell&gt;</code>开头的步骤进行操作</li>
<li>将<code>export PATH=/安装路径/mysql/bin:$PATH</code>添加到<code>/etc/profile</code></li>
<li><code>chkconfig mysqld on</code>或者<code>chkconfig mysqld.server on</code>视你的环境而定，详细步骤如下</li></ul></li>
<li rel="2"><p>MySQL 5.6.X 安装：</p>

<pre class="prettyprint hljs-dark"><code class="language-bash hljs">shell&gt; yum install libaio <span class="hljs-comment"># Debain系用户:apt-get install libaio1</span><br>shell&gt; groupadd mysql<br>shell&gt; useradd -r -g mysql mysql<br>shell&gt; <span class="hljs-built_in">cd</span> /usr/<span class="hljs-built_in">local</span><br>shell&gt; tar zxvf /path/to/mysql-VERSION-OS.tar.gz<br>shell&gt; ln <span class="hljs-operator">-s</span> full-path-to-mysql-VERSION-OS mysql<br>shell&gt; <span class="hljs-built_in">cd</span> mysql<br>shell&gt; chown -R mysql .<br>shell&gt; chgrp -R mysql .<br>shell&gt; scripts/mysql_install_db --user=mysql<br>shell&gt; chown -R root .<br>shell&gt; chown -R mysql data<br>shell&gt; bin/mysqld_safe --user=mysql &amp;<br><br><span class="hljs-comment"># Next command is optional</span><br><br>shell&gt; cp support-files/mysql.server /etc/init.d/mysql.server <br></code></pre></li>
<li rel="3"><p>MySQL 5.7.X 安装     </p>

<pre class="prettyprint hljs-dark"><code class="language-bash hljs">shell&gt; groupadd mysql<br>shell&gt; useradd -r -g mysql mysql<br>shell&gt; <span class="hljs-built_in">cd</span> /usr/<span class="hljs-built_in">local</span><br>shell&gt; tar zxvf /path/to/mysql-VERSION-OS.tar.gz<br>shell&gt; ln <span class="hljs-operator">-s</span> full-path-to-mysql-VERSION-OS mysql<br>shell&gt; <span class="hljs-built_in">cd</span> mysql<br>shell&gt; mkdir mysql-files<br>shell&gt; chmod <span class="hljs-number">770</span> mysql-files<br>shell&gt; chown -R mysql .<br>shell&gt; chgrp -R mysql .<br>shell&gt; bin/mysqld --initialize --user=mysql <span class="hljs-comment">#该步骤中会产生零时</span><br>                                            <span class="hljs-comment">#root@localhost密码</span><br>                                            <span class="hljs-comment">#需要自己记录下来</span><br>shell&gt; bin/mysql_ssl_rsa_setup          <br>shell&gt; chown -R root .<br>shell&gt; chown -R mysql data mysql-files<br>shell&gt; bin/mysqld_safe --user=mysql &amp;<br><br><span class="hljs-comment"># Next command is optional</span><br><br>shell&gt; cp support-files/mysql.server /etc/init.d/mysql.server<br></code></pre></li>
<li rel="4"><p>验证安装</p>

<ul>
<li><code>data</code>目录在安装之前是空目录，安装完成后应该有<code>ibXXX</code>等文件</li>
<li>安装过程中输出的信息中，不应该含有<code>ERROR</code>信息，错误信息<code>默认</code>会写入到<code>$HOSTNAME.err</code>的文件中</li>
<li>通过<code>bin/mysql</code>命令（<em>5.7.X含有零时密码</em>）可以正常登录</li></ul></li>
<li rel="5"><p>MySQL启动</p>

<ul>
<li><code>mysqld_safe --user=mysql &amp;</code> 即可启动，<code>mysqld_safe</code>是一个守护<code>mysqld</code>进程的脚本程序，旨在<code>mysqld</code>意外停止时，可以重启<code>mysqld</code>进程</li>
<li>也可以通过<code>INSTALL_BINARRY</code>中的的步骤，使用<code>/etc/init.d/mysql.server  start</code>进行启动（启动脚本以你复制的实际名字为准，通常改名为<code>mysqld</code>,即<code>/etc/init.d/mysqld start</code>）</li></ul></li>
</ol>

<hr>

</div><div id="wmd-preview-section-10177" class="wmd-preview-section preview-content">

<h2 id="五-附录">五. 附录</h2>

<ol><li rel="1"><p>姜老师的配置文件<code>my.cnf</code>   </p>

<pre class="prettyprint hljs-dark"><code class="language-bash hljs">[client]<br>user=david<br>password=<span class="hljs-number">88888888</span><br><br>[mysqld]<br><br><span class="hljs-comment">########basic settings########</span><br><br>server-id = <span class="hljs-number">11</span> <br>port = <span class="hljs-number">3306</span><br>user = mysql<br><span class="hljs-built_in">bind</span>_address = <span class="hljs-number">10.166</span>.<span class="hljs-number">224.32</span>   <span class="hljs-comment">#根据实际情况修改</span><br>autocommit = <span class="hljs-number">0</span>   <span class="hljs-comment">#5.6.X安装时，需要注释掉，安装完成后再打开</span><br>character_<span class="hljs-built_in">set</span>_server=utf8mb4<br>skip_name_resolve = <span class="hljs-number">1</span><br>max_connections = <span class="hljs-number">800</span><br>max_connect_errors = <span class="hljs-number">1000</span><br>datadir = /data/mysql_data      <span class="hljs-comment">#根据实际情况修改,建议和程序分离存放</span><br>transaction_isolation = READ-COMMITTED<br>explicit_defaults_<span class="hljs-keyword">for</span>_timestamp = <span class="hljs-number">1</span><br>join_buffer_size = <span class="hljs-number">134217728</span><br>tmp_table_size = <span class="hljs-number">67108864</span><br>tmpdir = /tmp<br>max_allowed_packet = <span class="hljs-number">16777216</span><br>sql_mode = <span class="hljs-string">"STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"</span><br>interactive_timeout = <span class="hljs-number">1800</span><br><span class="hljs-built_in">wait</span>_timeout = <span class="hljs-number">1800</span><br><span class="hljs-built_in">read</span>_buffer_size = <span class="hljs-number">16777216</span><br><span class="hljs-built_in">read</span>_rnd_buffer_size = <span class="hljs-number">33554432</span><br>sort_buffer_size = <span class="hljs-number">33554432</span><br><br><span class="hljs-comment">########log settings########</span><br><br><span class="hljs-built_in">log</span>_error = error.log<br>slow_query_<span class="hljs-built_in">log</span> = <span class="hljs-number">1</span><br>slow_query_<span class="hljs-built_in">log</span>_file = slow.log<br><span class="hljs-built_in">log</span>_queries_not_using_indexes = <span class="hljs-number">1</span><br><span class="hljs-built_in">log</span>_slow_admin_statements = <span class="hljs-number">1</span><br><span class="hljs-built_in">log</span>_slow_slave_statements = <span class="hljs-number">1</span><br><span class="hljs-built_in">log</span>_throttle_queries_not_using_indexes = <span class="hljs-number">10</span><br>expire_logs_days = <span class="hljs-number">90</span><br>long_query_time = <span class="hljs-number">2</span><br>min_examined_row_<span class="hljs-built_in">limit</span> = <span class="hljs-number">100</span><br><br><span class="hljs-comment">########replication settings########</span><br><br>master_info_repository = TABLE<br>relay_<span class="hljs-built_in">log</span>_info_repository = TABLE<br><span class="hljs-built_in">log</span>_bin = bin.log<br>sync_binlog = <span class="hljs-number">1</span><br>gtid_mode = on<br>enforce_gtid_consistency = <span class="hljs-number">1</span><br><span class="hljs-built_in">log</span>_slave_updates<br>binlog_format = row <br>relay_<span class="hljs-built_in">log</span> = relay.log<br>relay_<span class="hljs-built_in">log</span>_recovery = <span class="hljs-number">1</span><br>binlog_gtid_simple_recovery = <span class="hljs-number">1</span><br>slave_skip_errors = ddl_exist_errors<br><br><span class="hljs-comment">########innodb settings########</span><br><br>innodb_page_size = <span class="hljs-number">8192</span><br>innodb_buffer_pool_size = <span class="hljs-number">6</span>G    <span class="hljs-comment">#根据实际情况修改</span><br>innodb_buffer_pool_instances = <span class="hljs-number">8</span><br>innodb_buffer_pool_load_at_startup = <span class="hljs-number">1</span><br>innodb_buffer_pool_dump_at_shutdown = <span class="hljs-number">1</span><br>innodb_lru_scan_depth = <span class="hljs-number">2000</span><br>innodb_lock_<span class="hljs-built_in">wait</span>_timeout = <span class="hljs-number">5</span><br>innodb_io_capacity = <span class="hljs-number">4000</span><br>innodb_io_capacity_max = <span class="hljs-number">8000</span><br>innodb_flush_method = O_DIRECT<br>innodb_file_format = Barracuda<br>innodb_file_format_max = Barracuda<br>innodb_<span class="hljs-built_in">log</span>_group_home_dir = /redolog/  <span class="hljs-comment">#根据实际情况修改</span><br>innodb_undo_directory = /undolog/      <span class="hljs-comment">#根据实际情况修改</span><br>innodb_undo_logs = <span class="hljs-number">128</span><br>innodb_undo_tablespaces = <span class="hljs-number">3</span><br>innodb_flush_neighbors = <span class="hljs-number">1</span><br>innodb_<span class="hljs-built_in">log</span>_file_size = <span class="hljs-number">4</span>G               <span class="hljs-comment">#根据实际情况修改</span><br>innodb_<span class="hljs-built_in">log</span>_buffer_size = <span class="hljs-number">16777216</span><br>innodb_purge_threads = <span class="hljs-number">4</span><br>innodb_large_prefix = <span class="hljs-number">1</span><br>innodb_thread_concurrency = <span class="hljs-number">64</span><br>innodb_<span class="hljs-built_in">print</span>_all_deadlocks = <span class="hljs-number">1</span><br>innodb_strict_mode = <span class="hljs-number">1</span><br>innodb_sort_buffer_size = <span class="hljs-number">67108864</span> <br><br><span class="hljs-comment">########semi sync replication settings########</span><br><br>plugin_dir=/usr/<span class="hljs-built_in">local</span>/mysql/lib/plugin      <span class="hljs-comment">#根据实际情况修改</span><br>plugin_load = <span class="hljs-string">"rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"</span><br>loose_rpl_semi_sync_master_enabled = <span class="hljs-number">1</span><br>loose_rpl_semi_sync_slave_enabled = <span class="hljs-number">1</span><br>loose_rpl_semi_sync_master_timeout = <span class="hljs-number">5000</span><br><br>[mysqld-<span class="hljs-number">5.7</span>]<br>innodb_buffer_pool_dump_pct = <span class="hljs-number">40</span><br>innodb_page_cleaners = <span class="hljs-number">4</span><br>innodb_undo_<span class="hljs-built_in">log</span>_truncate = <span class="hljs-number">1</span><br>innodb_max_undo_<span class="hljs-built_in">log</span>_size = <span class="hljs-number">2</span>G<br>innodb_purge_rseg_truncate_frequency = <span class="hljs-number">128</span><br>binlog_gtid_simple_recovery=<span class="hljs-number">1</span><br><span class="hljs-built_in">log</span>_timestamps=system<br>transaction_write_<span class="hljs-built_in">set</span>_extraction=MURMUR32<br>show_compatibility_56=on<br></code></pre></li>
<li rel="2"><p>几个重要的参数配置和说明 </p>

<ul>
<li><p><code>innodb_log_file_size = 4G</code> :做实验可以更改的小点，线上环境推荐用4G，以前5.5和5.1等版本之所以官方给的值很小，是因为太大后有bug，现在bug已经修复</p></li>
<li><p><code>innodb_undo_logs = 128</code>和<code>innodb_undo_tablespaces = 3</code>建议在安装之前就确定好该值，后续修改比较麻烦</p></li>
<li><code>[mysqld]</code>，<code>[mysqld-5.7]</code>这种tag表明了下面的配置在什么版本下才生效,<code>[mysqld]</code>下均生效</li>
<li><code>autocommit</code>,这个参数在5.5.X以后才有，安装5.6.X的时候要注意先把该参数注释掉，等安装完成后，再行打开, 5.7.X无需预先注释</li>
<li><code>datadir</code>, <code>innodb_log_group_home_dir</code>, <code>innodb_undo_directory</code>一定要注意他的权限是 <code>mysql:mysql</code></li></ul></li>
<li rel="3"><p><code>my.cnf</code>问题</p>

<ul>
<li>使用<code>mysqld --help -vv | grep my.cnf</code>查看mysql的配置文件读取顺序</li>
<li>后读取的<code>my.cnf</code>中的配置，如果有相同项，会覆盖之前的配置</li>
<li>使用<code>--defaults-files</code>可指定配置文件</li></ul></li>
</ol></div><div id="wmd-preview-section-footnotes" class="preview-content"></div></div></body></html>